package com.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.bean.Table;
import com.db.DataBase;

public class TableDao extends DataBase {

	private String tableName = "T_TOOL";
	private boolean flag = false;
	
	public boolean add(Table obj){
		
		flag = true;
		StringBuilder sql  = new StringBuilder("INSERT ");
		sql.append( tableName);
		sql.append("(CREATE_TIME,MODIFY_TIME,P_ID,T_NAME,T_MODIFY,T_SEQUENCE) ");
		sql.append("VALUES(NOW(),NOW(),0,?,?,?)");
		
		try {
			preparedStatement = conn.prepareStatement(sql.toString());
			int index = 1;
			preparedStatement.setString(index++, obj.getName().toUpperCase().trim());
			preparedStatement.setString(index++, obj.getModify().trim());
			preparedStatement.setInt(index++, obj.getSeq());
			preparedStatement.execute();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			flag = false;
		}finally{
			close();
		}
		
		return flag;
	}
	
	public boolean del(long id){
		
		flag = true;
		
		StringBuilder sql  = new StringBuilder("UPDATE ");
		sql.append( tableName );
		sql.append(" SET MODIFY_TIME = NOW(),DEL_STATUS = 1");
		sql.append(" WHERE ID = ");
		sql.append(id);
		
		try {
			preparedStatement = conn.prepareStatement(sql.toString());
			preparedStatement.execute();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			flag = false;
		}finally{
			close();
		}
		
		return flag;
	}
	
	public boolean update(Table obj){
		
		flag = true;
		StringBuilder sql = new StringBuilder("UPDATE ");
		sql.append(tableName);
		sql.append(" SET MODIFY_TIME = NOW(),");
		sql.append(" T_NAME = ?,");
		sql.append(" T_MODIFY = ?, ");
		sql.append(" T_SEQUENCE = ? WHERE ID = ?");
		
		try {
			preparedStatement = conn.prepareStatement(sql.toString());
			int index = 1;
			preparedStatement.setString(index++, obj.getName().toUpperCase().trim());
			preparedStatement.setString(index++, obj.getModify().trim());
			preparedStatement.setInt(index++, obj.getSeq());
			preparedStatement.setLong(index++, obj.getId());
			preparedStatement.execute();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			flag = false;
		}finally{
			close();
		}
		
		return flag ;
	}
	
	public Table get(Long id){
		
		StringBuilder sql = new StringBuilder("SELECT ");
		sql.append("ID,");
		sql.append("CREATE_TIME,");
		sql.append("MODIFY_TIME,");
		sql.append("T_NAME,");
		sql.append("T_MODIFY,");
		sql.append("T_SEQUENCE ");
		sql.append("FROM ");
		sql.append(tableName);
		sql.append(" WHERE DEL_STATUS = 0 AND ID = ? ");
		
		try {
			preparedStatement = conn.prepareStatement(sql.toString());
			int index = 1;
			preparedStatement.setLong(index++, id);
			resultSet = preparedStatement.executeQuery();
			if(resultSet.next()){
				Table table = new Table();
				table.setId(resultSet.getLong("ID"));
				table.setCreateTime(resultSet.getTimestamp("CREATE_TIME"));
				table.setModifyTime(resultSet.getTimestamp("MODIFY_TIME"));
				table.setName(resultSet.getString("T_NAME"));
				table.setModify(resultSet.getString("T_MODIFY"));
				table.setSeq(resultSet.getInt("T_SEQUENCE"));
				return table;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		return null;
	}
	
	/**
	 * 
	 * @param name
	 * @return
	 */
	public Table getByName(String name){
		
		StringBuilder sql = new StringBuilder("SELECT ");
		sql.append("ID,");
		sql.append("CREATE_TIME,");
		sql.append("MODIFY_TIME,");
		sql.append("T_NAME,");
		sql.append("T_MODIFY,");
		sql.append("T_SEQUENCE ");
		sql.append("FROM ");
		sql.append(tableName);
		sql.append(" WHERE DEL_STATUS = 0 AND P_ID = 0 AND T_NAME = ? ");
		
		try {
			preparedStatement = conn.prepareStatement(sql.toString());
			int index = 1;
			preparedStatement.setString(index++, name.trim().toUpperCase());
			resultSet = preparedStatement.executeQuery();
			if(resultSet.next()){
				Table table = new Table();
				table.setId(resultSet.getLong("ID"));
				table.setCreateTime(resultSet.getTimestamp("CREATE_TIME"));
				table.setModifyTime(resultSet.getTimestamp("MODIFY_TIME"));
				table.setName(resultSet.getString("T_NAME"));
				table.setModify(resultSet.getString("T_MODIFY"));
				table.setSeq(resultSet.getInt("T_SEQUENCE"));
				return table;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		return null;
	}
	
	/**
	 *
	 * @param projectID
	 * @param name
	 * @return
	 */
	public List<Table> query(Long projectID ,String name){
		
		StringBuilder sql = new StringBuilder("SELECT ");
		sql.append("t.ID,");
		sql.append("t.CREATE_TIME,");
		sql.append("t.MODIFY_TIME,");
		sql.append("t.T_NAME,");
		sql.append("t.T_MODIFY,");
		sql.append("t.T_SEQUENCE,");
		sql.append("t.T_PROJECT_ID,");
		sql.append("p.NAME  ");
		sql.append("FROM ");
		sql.append(tableName + " t ");
		sql.append("LEFT JOIN t_project p ON p.ID = t.T_PROJECT_ID ");
		sql.append("WHERE 1=1 ");
		if(null != name && !"".equals(name)){
			sql.append("AND  t.T_NAME LIKE ? ");
		}
		sql.append("AND t.T_PROJECT_ID = ? ");
		sql.append("AND t.DEL_STATUS = 0 AND t.P_ID = 0 ");
		sql.append("ORDER BY t.T_SEQUENCE ASC,t.T_NAME ASC ");
		
		List<Table> objs = new ArrayList<Table>();
		
		try {
			
			preparedStatement = conn.prepareStatement(sql.toString());
			int index = 1;
			if(null != name && !"".equals(name)){
				preparedStatement.setString(index++, "%" + name.trim().toUpperCase() + "%");
			}
			preparedStatement.setLong(index++, projectID );
			resultSet = preparedStatement.executeQuery();
			
			Table table;
			while(resultSet.next()){
				table = new Table();
				table.setId(resultSet.getLong("ID"));
				table.setCreateTime(resultSet.getTimestamp("CREATE_TIME"));
				table.setModifyTime(resultSet.getTimestamp("MODIFY_TIME"));
				table.setName(resultSet.getString("T_NAME"));
				table.setModify(resultSet.getString("T_MODIFY"));
				table.setSeq(resultSet.getInt("T_SEQUENCE"));
				table.setProjectID(resultSet.getLong("T_PROJECT_ID"));
				table.setProjectName(resultSet.getString("NAME"));
				objs.add(table);
			}
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		
		return objs;
	}

}
